package com.wxxymakers.grademark.utils;

import com.wxxymakers.grademark.model.entity.WxItem;
import com.wxxymakers.grademark.model.entity.WxItemRecord;
import com.wxxymakers.grademark.model.entity.WxUser;
import jxl.Workbook;
import jxl.write.*;
import org.springframework.stereotype.Component;

import java.io.IOException;
import java.io.OutputStream;
import java.text.SimpleDateFormat;
import java.time.LocalDate;
import java.util.ArrayList;
import java.util.LinkedHashMap;
import java.util.List;
import java.util.Map;

/**
 * @AUTHOR soft
 * @DATE 2019/1/22 21:53
 * @DESCRIBE
 */
@Component
public class ExcelUtils {

    /**
     * 导出一次事件下的所有记录 统计好再进行导出
     *
     * @param items   所有评定项
     * @param records 事件下所有记录
     */
    public void exportWithIncident(List<WxItem> items, List<WxItemRecord> records,
                                   String collegeName, OutputStream out) throws IOException, WriteException {
        int index = 0;  // 序号计数
        int itemLen = items.size();
        SimpleDateFormat sf = new SimpleDateFormat("yyyy-MM-dd");

        WritableWorkbook workbook = Workbook.createWorkbook(out);
        WritableSheet sheet = workbook.createSheet("自动统计", 0);

        int year = LocalDate.now().getYear();
        String format = "皖西学院%d-%d聘期考核与分级聘任业绩成果积分核算表[%s]";

        sheet.addCell(new Label(0, 0, String.format(format, year - 1, year + 1, collegeName), header()));
        sheet.mergeCells(0, 0, itemLen + 8, 0);
        sheet.setRowView(0, 100 * 8);
        sheet.setRowView(1, 60 * 8);

        // 设置标题
        String[] fixTitle = {"序号", "所在单位 ", "姓 名", "性别", "出生年月", "现任职务.职级",
                "任现职时间", "晋级职务.职级"};
        for (int i = 0; i < itemLen + 8; i++) {
            String val;
            if (i < 8) { // 设置固定的用户信息表头
                val = fixTitle[i];
            } else { // 设置动态的评定项目表头
                WxItem item = items.get(i - 8);
                val = item.getName() + (item.getTisAdd() ? "（积分）" : "（减分）");
            }
            sheet.addCell(new Label(i, 1, val, title()));
            sheet.setColumnView(i, val.length() * 3);
        }
        sheet.addCell(new Label(itemLen + 8, 1, "总分", title()));

        // 设置用户记录内容
        Map<WxUser, List<WxItemRecord>> group = groupByUid(records);

        for (WxUser user : group.keySet()) {
            float totalScore = 0F; // 总分
            int row = index + 2; // 当前设置的行
            String sex;
            switch (user.getSex()) {
                case "1": sex = "男"; break;
                case "2": sex = "女"; break;
                default: sex = "未知"; break;
            }
            String dutyName = user.getDuty().getName();
            sheet.addCell(new Label(0, row, "" + (index + 1)));
            sheet.addCell(new Label(1, row, user.getDepartment().getName() + "[" + user.getCollege().getName() + "]"));
            sheet.addCell(new Label(2, row, user.getName()));
            sheet.addCell(new Label(3, row, sex));
            sheet.addCell(new Label(4, row, sf.format(user.getBirthday())));
            sheet.addCell(new Label(5, row, dutyName + "." + user.getDutyLevel() + "级"));
            sheet.addCell(new Label(6, row, sf.format(user.getNtime())));
            int i1 = user.getDutyLevel() - 1; if (i1 < 0) i1 = 0;
            sheet.addCell(new Label(7, row, dutyName + "." + i1 + "级"));

            List<WxItemRecord> userRecords = group.get(user);
            int col = 8;
            for (int i = 0; i < itemLen; i++, col++) {
                List<WxItemRecord> res = getRecordsByItemId(userRecords, items.get(i).getId());
                float score = countScore(res);
                sheet.addCell(new Label(col, row, String.format("共%d条：%.2f分", res.size(), score)));
                totalScore += score;
            }
            sheet.addCell(new Label(col, row, String.format("%.2f分", totalScore)));
            index++;
        }

        workbook.write();
        workbook.close();
    }


    /**
     * 根据用户id对所有记录进行分组
     *
     * @param records 所有记录
     * @return 分组后的map集合
     */
    private Map<WxUser, List<WxItemRecord>> groupByUid(List<WxItemRecord> records) {
        Map<WxUser, List<WxItemRecord>> userRecordMap = new LinkedHashMap<>();
        for (WxItemRecord record : records) {
            WxUser user = record.getUser();
            if (userRecordMap.containsKey(user)) {
                List<WxItemRecord> itemRecords = userRecordMap.get(user);
                itemRecords.add(record);
            } else {
                userRecordMap.put(user, new ArrayList<WxItemRecord>() {{
                    add(record);
                }});
            }
        }
        return userRecordMap;
    }

    /**
     * 通过评定项id在记录列表中查询记录
     *
     * @param records 记录列表
     * @param itemId  评定项id
     * @return 记录或null
     */
    private List<WxItemRecord> getRecordsByItemId(List<WxItemRecord> records, Integer itemId) {
        List<WxItemRecord> rs = new ArrayList<>();
        for (WxItemRecord record : records) {
            if (record.getItemId().equals(itemId)) {
                rs.add(record);
            }
        }
        return rs;
    }

    /**
     * 根据记录列表统计分数
     * @param records 记录列表
     * @return 分值
     */
    private float countScore(List<WxItemRecord> records) {
        float f = 0;
        for (WxItemRecord record : records) {
            f += record.getScore();
        }
        return f;
    }

    private WritableCellFormat header() throws WriteException {
        WritableCellFormat style = new WritableCellFormat();  // 单元格样式
        WritableFont font = new WritableFont(WritableFont.createFont("黑体"), 20);  // 字体
        font.setColour(Colour.BLACK);  // 设置字体颜色
        style.setAlignment(Alignment.CENTRE); // 设置单元格水平居中
        style.setVerticalAlignment(VerticalAlignment.CENTRE); // 设置单元格垂直居中
        style.setFont(font);  // 设置字体

        return style;
    }

    private WritableCellFormat title() throws WriteException {
        WritableCellFormat style = new WritableCellFormat();  // 单元格样式
        WritableFont font = new WritableFont(WritableFont.createFont("黑体"), 11);  // 字体
        font.setColour(Colour.BLACK);  // 设置字体颜色
        style.setAlignment(Alignment.CENTRE); // 设置单元格水平居中
        style.setVerticalAlignment(VerticalAlignment.CENTRE); // 设置单元格垂直居中
        style.setFont(font);  // 设置字体
        style.setWrap(true); // 自动换行

        return style;
    }
}
